;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;; Example crud generation and usage.  ;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;;;; Generating the crud

;; First generate a crud with the following commands.  Note that the
;; crud has to be regenerated whenever the database schema changes.

(require :autoproject.crud)

;; The adapter isn't part of the crud, since we don't want to
;; introduce a dependency to a particular database back-end.

(load "db-adapter-clsql")

;; This will inspect the database, and create class definitions based
;; on the schema, and produce the file "mydb-crud.lisp".

(autoproject.crud::write-crud-forms-to-file
 (autoproject.crud::make-crud 'autoproject.crud::crud
                              'autoproject.crud::clsql-db-adapter
                              :sqlite3 '("/home/john/mydb.db")) ;; clsql args
 "mydb-crud.lisp" ;; crud file 
 "MYDB"           ;; crud class
 "MYDB-CRUD")     ;; crud package

;; One might want to automate the above steps for easy crud
;; regeneration.


;;;; Using the crud

;; Add the db adapter and resulting crud file to your project
;; definition:

(asdf:defsystem #:myproject.crud-example
    :version "0"
    :description ""
    :depends-on (:autoproject.crud
                 :clsql)
    :serial t
    :components 
    ((:file "db-adapter-clsql")
     (:file "mydb-crud") ;; <-- the file we just created
     (:file "my-code-goes-here")))
 

;; The crud can now be used in the project:

;; A singleton instance is probably ok.
(defvar *crud* (autoproject.crud::make-crud 
                'mydb-crud:mydb  ; <-- the crud we just created
                'autoproject.crud::clsql-db-adapter
                :sqlite3 '("/home/john/mydb.db")))


(defun insert-foo (&key foo bar)
  (autoproject.crud:crud-create-from-instance
   *crud* (make-instance 'mydb-crud:foo 
                         :foo foo
                         :bar bar)))

;; Make some instances and save them:

(insert-foo :foo "Hello" :bar 42)

(insert-foo :foo "Goodbye" :bar 42)

(insert-foo :foo "Xyzzy" :bar 42)


;; Query the database by matching on a partial instance:

(let* ((partial-foo (make-instance 'mydb-crud:foo :bar 42))
       (foos (autoproject.crud:crud-match *crud* partial-foo)))
  
  ;; crud-match returns a set of instances
  (mapcar 'mydb-crud:get-foo foos))

;; ==> ("Hello" "Goodbye" "Xyzzy")


;; Or with a select:

(let* ((select (autoproject.crud::make-select :table "foo"
                                              :where "bar = 42"))
       (foos (autoproject.crud::crud-select *crud* select)))
  (mapcar 'mydb-crud:get-foo foos))

;; ==> ("Hello" "Goodbye" "Xyzzy")


;; Or with raw sql:

(let* ((select "select * from foo where bar = 42"))
  (autoproject.crud::crud-select-raw *crud* select))

;; which gives the default clsql output:

;; ==> (("Hello 42")
;;      ("Goodbye 42")
;;      ("Xyzzy 42"))
;;     ("foo" "bar")

